<html><head><script src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/i.js"></script>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>MySQL and Java JDBC - Tutorial</title>
<link href="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/styles3.css" rel="stylesheet" type="text/css">
<meta content="DocBook XSL Stylesheets V1.74.0" name="generator">
<meta name="description" content="MySQL and Java JDBC This article describes how to use Java JDBC to connect to MySQL and perform SQL queries, database inserts and deletes.">
</head><body link="#0000ff" text="black" vlink="#840084" alink="#0000ff" bgcolor="white">

<!-- Jennifers Analytics -->


<script type="text/javascript"> var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www."); document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E")); </script><script src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/ga.js" type="text/javascript"></script>
<script type="text/javascript"> var pageTracker = _gat._getTracker("UA-3967758-1"); pageTracker._initData(); pageTracker._trackPageview(); </script>

<script src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/flattr.html" type="text/javascript">
</script>

<!-- lakequincy.com -->
<script type="text/javascript">//
    if (typeof jQuery == 'undefined') {
        document.write(unescape("%3Cscript src='http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.4.2.min.js' type='text/javascript' %3E%3C/script%3E"));
    }//</script><script src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/jquery-1.js" type="text/javascript"></script>
<script type="text/javascript" language="Javascript" src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/m.js"></script>
<script type="text/javascript" language="Javascript"> if(jQuery.LqmAds)jQuery.LqmAds();</script>





<div id="topnav">

<a href="http://www.vogella.de/"> 

<h1> vogella.de </h1>

</a>
	
	
<ul id="home" class="topnav">
		
<li> 
<a title="Home" href="http://www.vogella.de/">Home</a>
</li>
		
<li>
<a title="Lars Vogel's Blog" href="http://www.vogella.de/blog">Blog </a>
</li>
		
<li>
<a title="Java Tutorials" href="http://www.vogella.de/java.html">Java </a>
</li>
		
<li>
<a title="Eclipse Tutorials" href="http://www.vogella.de/eclipse.html">Eclipse</a>
</li>
		
<li>
<a title="Android Tutorials" href="http://www.vogella.de/android.html">Android</a>
</li>
		
<li>
<a title="Google Tutorials" href="http://www.vogella.de/google.html">Google</a>
</li>
		
<li>
<a title="Web development Tutorials" href="http://www.vogella.de/web.html">Web</a>
</li>
		
<li>
<a title="Technology Tutorials" href="http://www.vogella.de/technology.html">Technology</a>
</li>
		
<li>
<a title="Algorithm Tutorials" href="http://www.vogella.de/algorithms.html">Algorithms</a>
</li>
		
<li>
<a title="Trainings Offerings from Lars" href="http://www.vogella.de/training/overview.html">Training</a>
</li>
	
</ul>


</div>
		




<div id="leftcol">

<!-- Google Ads -->

<script type="text/javascript" src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/google_service.js">
</script>
<script type="text/javascript">
GS_googleAddAdSenseService("ca-pub-3851443674951530");
GS_googleEnableAllServices();
</script>
<script type="text/javascript">
GA_googleAddSlot("ca-pub-3851443674951530", "Wide_Skyscraper");
</script>
<script type="text/javascript">
GA_googleFetchAds();
</script>

<!-- Google Ads-->
<script type="text/javascript">
GA_googleFillSlot("Wide_Skyscraper");
</script>


</div>




<div id="rightcol">

<a href="http://www.vogella.de/about.html"> 
	<img src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/LarsVogelArticle7.png" alt="About Lars Vogel" height="115" width="125"> 
</a>

<br>


<a href="http://www.vogella.de/training/android.html"> 
<img src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/trainings.png" alt="Android Training (in Germany)" width="200"> 
 </a>


<br>

<br>

<!-- lakequincy.com -->

<div class="lqm_ad" lqm_channel="1" lqm_publisher="9185" lqm_zone="1" lqm_format="18"></div>


<br>

<br>




<div class="twitter">

<a href="http://www.twitter.com/vogella"> 
	<img src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/twitter.png" alt="Follow me on twitter"> 
</a>

</div>


</div>





<div class="article" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title">
<a name="N10001"></a>MySQL and Java JDBC - Tutorial</h2>
</div>
<div>
<div class="author">
<h3 class="author">
<span class="firstname">Lars</span> <span class="surname">Vogel</span>
</h3>
</div>
</div>
<div>
<p class="releaseinfo">
			
</p><p>Version 0.4</p>
		
<p></p>
</div>
<div>
<p class="copyright">Copyright © 2009  Lars Vogel</p>
</div>
<div>
<p class="pubdate">14.09.2009</p>
</div>
<div>
<div class="revhistory">
<table summary="Revision history" width="100%" border="1">
<tbody><tr>
<th colspan="3" valign="top" align="left"><b>Revision History</b></th>
</tr>
<tr>
<td align="left">Revision 0.1</td><td align="left">25.05.2008</td><td align="left">Lars Vogel</td>
</tr>
<tr>
<td colspan="3" align="left">Split Java part from
					http://www.vogella.de/articles/MySQL/article.html</td>
</tr>
<tr>
<td align="left">Revision 0.2</td><td align="left">12.09.2009</td><td align="left">Lars Vogel</td>
</tr>
<tr>
<td colspan="3" align="left">Rework</td>
</tr>
<tr>
<td align="left">Revision 0.3</td><td align="left">13.09.2009</td><td align="left">Lars Vogel</td>
</tr>
<tr>
<td colspan="3" align="left">JDBC described</td>
</tr>
<tr>
<td align="left">Revision 0.4</td><td align="left">14.09.2009</td><td align="left">Lars Vogel</td>
</tr>
<tr>
<td colspan="3" align="left">Metadata </td>
</tr>
</tbody></table>
</div>
</div>
<div>
<div class="abstract">
<a name="abstract"></a>
<p class="title">
<b>MySQL and Java JDBC</b>
</p>
<p> This article describes how to use Java JDBC to connect to
				MySQL and
				perform SQL queries, database inserts and deletes.</p>
</div>
</div>
</div>
<hr>
</div>
<div class="toc">
<p>
<b>Table of Contents</b>
</p>
<dl>
<dt>
<span class="sect1"><a href="#jdbc">1. Java Database Connectivity (JDBC) </a></span>
</dt>
<dt>
<span class="sect1"><a href="#overview">2. Introduction to MySQL </a></span>
</dt>
<dt>
<span class="sect1"><a href="#jdbcdriver">3. MySQL JDBC driver </a></span>
</dt>
<dt>
<span class="sect1"><a href="#database">4. Create example database</a></span>
</dt>
<dt>
<span class="sect1"><a href="#javaconnection">5. Java JDBC </a></span>
</dt>
<dt>
<span class="sect1"><a href="#thankyou">6. Thank you </a></span>
</dt>
<dt>
<span class="sect1"><a href="#questions">7. Questions and Discussion</a></span>
</dt>
<dt>
<span class="sect1"><a href="#resources">8. Links and Literature</a></span>
</dt>
</dl>
</div> 

<script type="text/javascript">
<!--
google_ad_client = "ca-pub-3851443674951530";
/* Article - Nach der Gliederung */
google_ad_slot = "6440098216";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript" src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/show_ads.js">
</script>

<div class="sect1" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both;">
<a name="jdbc"></a>1.&nbsp;Java Database Connectivity (JDBC) </h2>
</div>
</div>
</div>
<p>
		The interface for accessing relational databases from Java is
		JDBC. Via JDBC you maintain the databases connection, issue database
		queries and updates and receive the results.
	</p>
<p>JDBC is an interface independently of any database. For each
		database you require the database specific implementation of the JDBC
		driver. </p>
</div>
<div class="sect1" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both;">
<a name="overview"></a>2.&nbsp;Introduction to MySQL </h2>
</div>
</div>
</div>
<p>
		To learn to install and use MySQL please
		see
		<a class="ulink" href="http://www.vogella.de/articles/MySQL/article.html" target="_top"> MySQL - Tutorial</a>
		.
	</p>
<p> The following will assume that you have successfully installed
		MySQL and know how to access MySQL via the command line. </p>
</div>
<div class="sect1" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both;">
<a name="jdbcdriver"></a>3.&nbsp;MySQL JDBC driver </h2>
</div>
</div>
</div>
<p>
		To connect to MySQL from Java you have to use the JDBC driver
		from
		MySQL. The MySQL JDBC driver is called "MySQL Connector/J". You should
		be able to find the latest MySQL JDBC driver on this
		page
		<a class="ulink" href="http://dev.mysql.com/downloads/connector/j" target="_top">http://dev.mysql.com/downloads/connector/j </a>
		.
	</p>
<p> The download contains a jar file which we require later. </p>
</div>
<div class="sect1" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both;">
<a name="database"></a>4.&nbsp;Create example database</h2>
</div>
</div>
</div>
<p> Start the MySQL command line client and paste the following SQL
		script into it to create a sample database which we can use as an
		example.</p>
<p>
		
</p><pre class="programlisting">			
create database feedback;
use feedback;
CREATE USER sqluser IDENTIFIED BY 'sqluserpw'; 
grant usage on *.* to sqluser@localhost identified by 'sqluserpw'; 
grant all privileges on feedback.* to sqluser@localhost;

CREATE TABLE COMMENTS (
		id INT NOT NULL AUTO_INCREMENT, 
		MYUSER VARCHAR(30) NOT NULL,
		EMAIL VARCHAR(30), 
		WEBPAGE VARCHAR(100) NOT NULL, 
		DATUM DATE NOT NULL, 
		SUMMERY VARCHAR(40) NOT NULL,
		COMMENTS VARCHAR(400) NOT NULL,
		PRIMARY KEY (ID)
	);

INSERT INTO COMMENTS values (default, 'lars', 'myemail@gmail.com','http://www.vogella.de', '2009-09-14 10:33:11', 'Summery','My first comment' );

		</pre>
	
<p></p>
</div> 

<script type="text/javascript">
<!--
google_ad_client = "pub-3851443674951530";
/* 728x90, Erstellt 06.11.08, Introduction */
google_ad_slot = "8639282357";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript" src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/show_ads.js">
</script>

<div class="sect1" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both;">
<a name="javaconnection"></a>5.&nbsp;Java JDBC </h2>
</div>
</div>
</div>
<p> Create a Java project and a package "de.vogella.mysql.first".
	</p>
<p>
		Create a folder "lib" and copy the JDBC driver into this folder.
		Add
		the JDBC driver to your classpath. See
		<a class="ulink" href="http://www.vogella.de/articles/Eclipse/article.html#classpath" target="_top"> Adding jars to the classpath</a>
		for details.
	</p>
<p> Create the following class to connect to the
		MySQL
		database and
		perform queries, inserts and deletes. It also prints the metadata
		(table name, column names) of a query result. </p>
<p>
		
</p><pre class="programlisting">			
package de.vogella.mysql.first;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class MySQLAccess {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public void readDataBase() throws Exception {
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connect = DriverManager
					.getConnection("jdbc:mysql://localhost/feedback?"
							+ "user=sqluser&amp;password=sqluserpw");

			// Statements allow to issue SQL queries to the database
			statement = connect.createStatement();
			// Result set get the result of the SQL query
			resultSet = statement
					.executeQuery("select * from FEEDBACK.COMMENTS");
			writeResultSet(resultSet);

			// PreparedStatements can use variables and are more efficient
			preparedStatement = connect
					.prepareStatement("insert into  FEEDBACK.COMMENTS values (default, ?, ?, ?, ? , ?, ?)");
			// "myuser, webpage, datum, summery, COMMENTS from FEEDBACK.COMMENTS");
			// Parameters start with 1
			preparedStatement.setString(1, "Test");
			preparedStatement.setString(2, "TestEmail");
			preparedStatement.setString(3, "TestWebpage");
			preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
			preparedStatement.setString(5, "TestSummary");
			preparedStatement.setString(6, "TestComment");
			preparedStatement.executeUpdate();

			preparedStatement = connect
					.prepareStatement("SELECT myuser, webpage, datum, summery, COMMENTS from FEEDBACK.COMMENTS");
			resultSet = preparedStatement.executeQuery();
			writeResultSet(resultSet);

			// Remove again the insert comment
			preparedStatement = connect
			.prepareStatement("delete from FEEDBACK.COMMENTS where myuser= ? ; ");
			preparedStatement.setString(1, "Test");
			preparedStatement.executeUpdate();
			
			resultSet = statement
			.executeQuery("select * from FEEDBACK.COMMENTS");
			writeMetaData(resultSet);
			
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}

	}

	private void writeMetaData(ResultSet resultSet) throws SQLException {
		// 	Now get some metadata from the database
		// Result set get the result of the SQL query
		
		System.out.println("The columns in the table are: ");
		
		System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
		for  (int i = 1; i&lt;= resultSet.getMetaData().getColumnCount(); i++){
			System.out.println("Column " +i  + " "+ resultSet.getMetaData().getColumnName(i));
		}
	}

	private void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		while (resultSet.next()) {
			// It is possible to get the columns via name
			// also possible to get the columns via the column number
			// which starts at 1
			// e.g. resultSet.getSTring(2);
			String user = resultSet.getString("myuser");
			String website = resultSet.getString("webpage");
			String summery = resultSet.getString("summery");
			Date date = resultSet.getDate("datum");
			String comment = resultSet.getString("comments");
			System.out.println("User: " + user);
			System.out.println("Website: " + website);
			System.out.println("Summery: " + summery);
			System.out.println("Date: " + date);
			System.out.println("Comment: " + comment);
		}
	}

	// You need to close the resultSet
	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

}

		</pre>
	
<p></p>
<p> Create the following main program to test your class.</p>
<p>
		
</p><pre class="programlisting">			
package de.vogella.mysql.first.test;

import de.vogella.mysql.first.MySQLAccess;

public class Main {
	public static void main(String[] args) throws Exception {
		MySQLAccess dao = new MySQLAccess();
		dao.readDataBase();
	}


}

		</pre>
	
<p></p>
</div>
<div class="sect1" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both;">
<a name="thankyou"></a>6.&nbsp;Thank you </h2>
</div>
</div>
</div>
	
<p></p>
	Please help me to support this article:
	<table>
<tbody><tr>
			
<td>
				<a class="FlattrButton" style="display: none;" rev="flattr;button:compact;" href="http://www.vogella.de/"></a>
			</td>

			<td>
				
<form action="https://www.paypal.com/cgi-bin/webscr" method="post">
					
<input name="cmd" value="_s-xclick" type="hidden">
					<input name="hosted_button_id" value="10463995" type="hidden">
					<input src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/btn_donateCC_LG.gif" name="submit" alt="PayPal - The safer, easier way to pay online!" type="image" border="0">
					<img alt="" src="Tutorial%20Mysql%20Java%20JDBC%20-%20Lars%20Vogel_files/pixel.gif" height="1" width="1" border="0">
				</form>
			
</td>
		
</tr>
</tbody></table>



</div>
<div class="sect1" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both;">
<a name="questions"></a>7.&nbsp;Questions and Discussion</h2>
</div>
</div>
</div>
<p>
		Before posting questions, please see the
		<a class="ulink" href="http://www.vogella.de/faq.html" target="_top"> vogella FAQ</a>
		. If you have questions or find an error in this article please
		use the
		<a class="ulink" href="http://groups.google.com/group/vogella" target="_top"> www.vogella.de Google Group</a>
		. I have created a short list
		<a class="ulink" href="http://www.vogella.de/blog/2010/03/09/asking-community-questions/" target="_top"> how to create good questions
		</a> which might also help you.
	</p>
</div>
<div class="sect1" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both;">
<a name="resources"></a>8.&nbsp;Links and Literature</h2>
</div>
</div>
</div>
<p>
		
<a class="ulink" href="http://www.mysql.com/" target="_top"> http://www.mysql.com</a>
		MySQL homepage
	</p>
<p>
		
<a class="ulink" href="http://dev.mysql.com/downloads/" target="_top"> http://dev.mysql.com/downloads/</a>
		Download link for MySQL
	</p>
</div>
</div>



</body></html>